<?
require_once($_SERVER["DOCUMENT_ROOT"] . '/db/db.php');
$file = 'query.kml';
$where = "";
header('Content-type: application/vnd.google-earth.kml+xml');
header('Content-disposition: attachment; filename='. $file);
// Opens a connection to a db server.
$db = new DBConnection('dbname=assclap_realty port=5432 user=assclap_assclap password=fibo1235');
	
/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
	$sLimit = "LIMIT " . addslashes( $_GET['iDisplayStart'] ).", ".
		addslashes( $_GET['iDisplayLength'] );
}

/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
	$sOrder = "ORDER BY  ";
	for ( $i=0 ; $i<addslashes( $_GET['iSortingCols'] ) ; $i++ )
	{
		$sOrder .= fnColumnToField(addslashes( $_GET['iSortCol_'.$i] ))."
			".addslashes( $_GET['sSortDir_'.$i] ) .", ";
	}
	$sOrder = substr_replace( $sOrder, "", -2 );
}
	
/* Filtering - NOTE this does not match the built-in DataTables filtering which does it
 * word by word on any field. It's possible to do here, but concerned about efficiency
 * on very large tables, and MySQL's regex functionality is very limited
 */
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
	$sWhere = "WHERE ( address ILIKE '%".addslashes( $_GET['sSearch'] )."%' OR ".
					"city ILIKE '%".addslashes( $_GET['sSearch'] )."%' OR ".
					"county ILIKE '%".addslashes( $_GET['sSearch'] )."%' OR ".
					"zip ILIKE '%".addslashes( $_GET['sSearch'] )."%' OR ".
					"price ILIKE '%".addslashes( $_GET['sSearch'] )."%' )";
}

for ( $i=0 ; $i<$_GET['iColumns'] ; $i++ )
{
	if ( $_GET['sSearch_'.$i] != '' )
	{
		if ( $sWhere != "" )
		{
			$sWhere .= " AND ";
		}
		else
		{
			$sWhere .= "WHERE ";
		}
		$sWhere .= fnColumnToField($i) ." ILIKE '%".addslashes( $_GET['sSearch_'.$i] )."%'";
	}
}
	
$sQuery = "select * from vw_property $sWhere $sOrder $sLimit";
$rResult = $db->CreateQuery($sQuery);

$iFilteredTotal = $rResult->AffectedRows();

$sQuery = "select count(*) as count from vw_property";
$rResultTotal = $db->CreateQuery($sQuery);
$oResultTotal = $rResultTotal->FetchObject();
$iTotal = $oResultTotal->count;

$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
$sOutput .= '"iTotalRecords": ' . $iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": ' . $iFilteredTotal . ', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = $rResult->FetchRow() )
{
	$sOutput .= "[";
	$sOutput .= '"'.str_replace('"', '\"', $aRow['id']).'",';
	$sOutput .= '"'.str_replace('"', '\"', $aRow['rating']).'",';
	$sOutput .= '"'.str_replace('"', '\"', $aRow['address']).'",';
	$sOutput .= '"'.str_replace('"', '\"', $aRow['city']).'",';
	$sOutput .= '"'.str_replace('"', '\"', $aRow['county']).'",';
	$sOutput .= '"'.str_replace('"', '\"', $aRow['zip']).'",';
	$sOutput .= '"'.str_replace('"', '\"', $aRow['price']).'",';
	$sOutput .= '"'.str_replace('"', '\"', $aRow['lon']).'",';
	$sOutput .= '"'.str_replace('"', '\"', $aRow['lat']).'"';
	$sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';

echo $sOutput;

function fnColumnToField( $i )
{
	if ( $i == 0 )
		return "id";
	else if ( $i == 1 )
		return "address";
	else if ( $i == 2 )
		return "city";
	else if ( $i == 3 )
		return "county";
	else if ( $i == 4 )
		return "zip";
	else if ( $i == 5 )
		return "price";
	else if ( $i == 6 )
		return "rating";
	else if ( $i == 7 )
		return "lon";
	else if ( $i == 8 )
		return "lat";
}
?>